/**
 * @author Stephan Marc Hansen
 */
package com.kea.firsthorse.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MysqlController implements MysqlControlIface {
	private Connection connect = null;

	@Override
	public void connect() throws Exception {
		try {
			System.out.print("Connecting to mysql....");
			String driverName = "com.mysql.jdbc.Driver"; //
			Class.forName(driverName);
			String server = "localhost";
			String database = "heste";
			String url = "jdbc:mysql://" + server + "/" + database;
			String username = "root";
			String password = "xy4711"; 		
			connect = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
		System.out.println("Hov vi kunne ikke finde databasedriveren.....");
		} catch (SQLException e) {
		System.out.println("Vi kunne ikke forbinde til databasen");
		System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
		System.out.println(e.getCause());
		System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
		} 
	 catch (Exception e) {
		System.out.println("mååååre trouble");
		System.out.println(e.getMessage());
		} 
	}

	@Override
	public ResultSet getUserPWHash(int id) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connect
					.prepareStatement("SELECT password from users where user_id = (?)");
			preparedStatement.setString(1, Integer.toString(id));
			resultSet = preparedStatement.executeQuery();
		}catch (SQLException e) {
			System.out.println("Fejl i select");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			} 
		catch (Exception e) {
			System.err.println("Error message:" + e.getMessage());
			System.err.println("Error number:"
					+ ((SQLException) e).getErrorCode());
		}
		
	return resultSet;
	}

	@Override
	public ResultSet getUserbyID(int id) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connect
					.prepareStatement("SELECT * from users where user_id = (?)");
			preparedStatement.setString(1, Integer.toString(id));
			resultSet = preparedStatement.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("Fejl i select");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			} 
		catch (Exception e) {
			System.err.println("Error message:" + e.getMessage());
			System.err.println("Error number:"
					+ ((SQLException) e).getErrorCode());
		}
		return resultSet;

	}

	@Override
	public ResultSet getPoliciesbyUserID(int id) throws Exception { //TODO: tager kun højde for hestepolicer pt.
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connect
					.prepareStatement("SELECT * FROM horse_policies_fields hpf JOIN policies p ON p.policy_id = hpf.policy_id WHERE p.user_id = (?);");
			preparedStatement.setString(1, Integer.toString(id));
			resultSet = preparedStatement.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("Fejl i select");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			} 
		catch (Exception e) {
			System.err.println("=Error message:" + e.getMessage());
			System.err.println("=Error number:"
					+ ((SQLException) e).getErrorCode());
		}
		return resultSet;

	}

	@Override
	public ResultSet getHorsebyPolicyID(int id) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connect
					.prepareStatement("select h.* FROM horses h join horse_policies_fields hpf ON hpf.horse_id = h.horse_id where hpf.policy_id = (?);");
			preparedStatement.setString(1, Integer.toString(id));
			resultSet = preparedStatement.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("Fejl i select");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			}
		catch (Exception e) {
			System.err.println("Error message:" + e.getMessage());
			System.err.println("Error number:"
					+ ((SQLException) e).getErrorCode());
		}
		return resultSet;

	}

	@Override
	public ResultSet getTermsbyPolicyID(int id) throws Exception {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connect
					.prepareStatement("select t.* from terms t join policies p on p.terms = t.term_id where p.policy_id = (?);");
			preparedStatement.setString(1, Integer.toString(id));
			resultSet = preparedStatement.executeQuery();
		}
		catch (SQLException e) {
			System.out.println("Fejl i select");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			}
		catch (Exception e) {
			System.err.println("Error message:" + e.getMessage());
			System.err.println("Error number:"
					+ ((SQLException) e).getErrorCode());
		}
		return resultSet;

	}	
	
	
	
	@Override
	public void disconnect() {
		try {
			if (connect != null) {
				connect.close();
			}
		}catch (SQLException e) {
			System.out.println("Vi kunne ikke afbryde forbindelsen til databasen");
			System.out.println(e.getErrorCode() + " :-> " + e.getMessage());
			System.out.println(e.getCause());
			System.out.println("^^^^^^^^^^^^^^^^^000^^^^^^^^^^^^^^^^^^^^^^^");
			} 
		catch (Exception e) {
			System.err.println("Error message:" + e.getMessage());
			System.err.println("Error number:"
					+ ((SQLException) e).getErrorCode());

		}
	}

}
